<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Ipkd_model extends CI_Model
{
    
    function __construct()
    {
       parent::__construct();
		$this->CI = get_instance();
    }//end of function

    function cek_id_tgl(){        
        $id = mysql_insert_id();
        return $id;
    }

    function cek_tgl(){        
        $this->db->select('*');
        $this->db->from('IPKD');
    }

    function add_ipkd($data){
        
        $this->db->insert('IPKD',$data);
        
    }//end of function
    
    function edit_ipkd($id_ipkd,$data){
        
        $this->db->where('IDIPKD',$id_ipkd);
        $this->db->update('IPKD',$data);        
        
    }//end of function
    
    function delete_ipkd($ipkd_id){
		$data = array(
			'STATUS' => 0
		);
		$this->db->where('IDIPKD',$ipkd_id);
		$this->db->update('IPKD',$data);
	}

    function get_ipkd(){
        
        $this->db->select('*');
        $this->db->from('IPKD');
        $this->db->where('STATUS > 0');
        $query = $this->db->get();
        return $query;        
        
    }//end of function
    
	function get_id_ipkd($id_ipkd){
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where('IDIPKD',$id_ipkd);
		$query = $this->db->get();
		return $query;
	}

	function get_sum_ipkd(){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE STATUS = 1 "; 
		return $this->db->query($sql);
	}

	function get_sum_ipkd_tahun($tahun){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1 "; 
		return $this->db->query($sql, array($tahun));
	}

	function get_ipkd_tahun($tahun){
		$sql = "SELECT * FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1 "; 
		return $this->db->query($sql, array($tahun));
	}

	function get_sum_ipkd_bulan($bulan1,$bulan2,$tahun){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1 "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun));
	}

	function get_ipkd_bulan($bulan1,$bulan2,$tahun){
		$sql = "SELECT * FROM IPKD WHERE MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1 "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun));
	}

	function get_sum_ipkd_bulan_kecamatan($bulan1,$bulan2,$tahun,$kecamatan){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND STATUS = 1 "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$kecamatan));
	}

	function get_ipkd_bulan_kecamatan($bulan1,$bulan2,$tahun,$kecamatan){
		$sql = "SELECT * FROM IPKD WHERE MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND STATUS = 1 "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$kecamatan));
	}

	function get_sum_ipkd_tahun_kecamatan($tahun,$kecamatan){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND STATUS = 1 "; 
		return $this->db->query($sql, array($tahun,$kecamatan));
	}

	function get_ipkd_tahun_kecamatan($tahun,$kecamatan){
		$sql = "SELECT * FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND STATUS = 1 "; 
		return $this->db->query($sql, array($tahun,$kecamatan));
	}

	function get_sum_ipkd_kecamatan($kecamatan){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD WHERE KECAMATAN = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1 "; 
		return $this->db->query($sql, array($kecamatan));
	}

	function get_ipkd_tunggakan(){
		$sql = "SELECT * FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) <= YEAR(CURDATE()) AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1";
		return $this->db->query($sql);
	}

	function get_sum_ipkd_tunggakan(){
		$sql = "SELECT SUM(`RETRIBUSI`) AS JUMLAH_RETRIBUSI FROM IPKD WHERE YEAR(`TGL_PEMBAYARAN`) <= YEAR(CURDATE()) AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND STATUS = 1";
		return $this->db->query($sql);
	}

	function get_tahun_value($id_tahun){
		$this->db->select('TAHUN');
		$this->db->from('TAHUN');
		$this->db->where('IDTAHUN',$id_tahun);
		$query = $this->db->get();
		return $query;
	}
	
	function get_ipkd_grid(){	
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where("STATUS > 0 ");
		$this->db->order_by("TAHUN", "asc"); 
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where("STATUS > 0 ");
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}

	function get_tanggal_grid($id_ipkd){	
		$this->db->select('*');
		$this->db->from('PEMBAYARAN');
		$this->db->where("STATUS > 0 ");
		$this->db->where('IDIPKD',$id_ipkd);
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('PEMBAYARAN');
		$this->db->where("STATUS > 0 ");
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}

	function get_tunggakan_grid(){

		$this->db->select('*');
		$this->db->from('TUNGGAKAN');
		$this->db->where("STATUS_BAYAR = 0 ");
		$this->db->where("YEAR(`TGL_PEMBAYARAN`) <= YEAR(CURDATE())");
		$this->db->where("MONTH(`TGL_PEMBAYARAN`) <= MONTH(CURDATE())");
		
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('TUNGGAKAN');
		$this->db->where("STATUS_BAYAR = 0 ");
		$this->db->where("YEAR(`TGL_PEMBAYARAN`) <= YEAR(CURDATE())");
		$this->db->where("MONTH(`TGL_PEMBAYARAN`) <= MONTH(CURDATE())");
		
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}

	function get_ijin_grid(){

		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where("STATUS > 0 ");
		$this->db->where("YEAR(`SAMPAI_PERIJINAN`) <= YEAR(CURDATE())");
		$this->db->where("MONTH(`SAMPAI_PERIJINAN`) <= MONTH(CURDATE())");
		
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where("STATUS > 0 ");
		$this->db->where("YEAR(`SAMPAI_PERIJINAN`) <= YEAR(CURDATE())");
		$this->db->where("MONTH(`SAMPAI_PERIJINAN`) <= MONTH(CURDATE())");
		
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}
	
	function get_all_kecamatan(){
		$this->db->select('*');
		$this->db->from('KECAMATAN');
		$this->db->where('STATUS',1);
		
		return $this->db->get();
	}
	
	function get_all_kelurahan(){
		$this->db->select('*');
		$this->db->from('KELURAHAN');
		$this->db->where('STATUS',1);
		
		return $this->db->get();
	}
	
	function get_klr_by_kcm($kcm){
		$this->db->select('*');
		$this->db->from('KELURAHAN');
		$this->db->where('IDKECAMATAN', $kcm);
		$this->db->where('STATUS', 1);
		
		return $this->db->get();
	}
	
	function get_kcm_by_id($id){
		$this->db->select('NAMA_KECAMATAN');
		$this->db->from('KECAMATAN');
		$this->db->where('IDKECAMATAN', $id);
		
		return $this->db->get();
	}
	
	function get_kcm_by_name($name){
		$this->db->select('*');
		$this->db->from('KECAMATAN');
		$this->db->where('NAMA_KECAMATAN', $name);
		
		return $this->db->get();
	}
	
	function get_klr_by_name($name){
		$this->db->select('*');
		$this->db->from('KELURAHAN');
		$this->db->where('NAMA_KELURAHAN', $name);
		
		return $this->db->get();
	}
	
	function get_all_ipkd_cari($tahun,$kecamatan){
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('KECAMATAN', $kecamatan);
		
		return $this->db->get()->result();
	}
	
	function get_all_dpb_cari($tahun,$kecamatan){
		$this->db->select('*');
		$this->db->from('PEIL_BANJIR');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('KECAMATAN', $kecamatan);
		
		return $this->db->get()->result();
	}
	
	function get_all_dibsa_cari($tahun,$kecamatan){
		$this->db->select('*');
		$this->db->from('IBSA');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('KECAMATAN', $kecamatan);
		
		return $this->db->get()->result();
	}
	
	function get_all_ipkd_thn($tahun){
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('STATUS',1);
		
		return $this->db->get()->result();
	}
	
	function get_all_dpb_thn($tahun){
		$this->db->select('*');
		$this->db->from('PEIL_BANJIR');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('STATUS',1);

		return $this->db->get()->result();
	}
	
	function get_all_dibsa_thn($tahun){
		$this->db->select('*');
		$this->db->from('IBSA');
		$this->db->where('TAHUN', $tahun);
		$this->db->where('STATUS',1);
		
		return $this->db->get()->result();
	}

	// buat bulan.....................................

	function get_all_ipkd_cari_bulan($tgl1,$tgl2,$kecamatan){
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where('TGL_PEMBAYARAN >=', $tgl1);
		$this->db->where('TGL_PEMBAYARAN <=', $tgl2);
		$this->db->where('KECAMATAN', $kecamatan);
		
		return $this->db->get()->result();
	}

	function get_all_ipkd_bln($tgl1,$tgl2){
		$this->db->select('*');
		$this->db->from('IPKD');
		$this->db->where('TGL_PEMBAYARAN >=', $tgl1);
		$this->db->where('TGL_PEMBAYARAN <=', $tgl2);
		$this->db->where('STATUS',1);
		
		return $this->db->get()->result();
	}

	// buat tgl pembayaran..................

	function add_tgl($data){
        
        $this->db->insert('PEMBAYARAN',$data);
        
    }//end of function
    
    function edit_tgl($id_bayar){
        $data = array(
			'STATUS_BAYAR' => 1
		);
        $this->db->where('IDBAYAR',$id_bayar);
        $this->db->update('PEMBAYARAN',$data);        
        
    }//end of function

    function update_tgl($id_bayar,$data){
        
        $this->db->where('IDBAYAR',$id_bayar);
        $this->db->update('PEMBAYARAN',$data);  
        
        
    }
    
    function delete_tgl($bayar_id){
		$data = array(
			'STATUS' => 0
		);
		$this->db->where('IDBAYAR',$bayar_id);
		$this->db->update('PEMBAYARAN',$data);
	}

	function get_sum_ipkd_bayar($status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($status));
	}

	function get_sum_ipkd_tahun_bayar($tahun, $status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($tahun, $status));
	}

	function get_ipkd_tahun_bayar($tahun, $status){
		$sql = "SELECT * FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($tahun, $status));
	}

	function get_sum_ipkd_bulan_bayar($bulan1,$bulan2,$tahun, $status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$status));
	}

	function get_ipkd_bulan_bayar($bulan1,$bulan2,$tahun,$status){
		$sql = "SELECT * FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$status));
	}

	function get_sum_ipkd_bulan_kecamatan_bayar($bulan1,$bulan2,$tahun,$kecamatan,$status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$kecamatan,$status));
	}

	function get_ipkd_bulan_kecamatan_bayar($bulan1,$bulan2,$tahun,$kecamatan,$status){
		$sql = "SELECT * FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND MONTH(`TGL_PEMBAYARAN`) >= ? AND MONTH(`TGL_PEMBAYARAN`) <= ? AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($bulan1,$bulan2,$tahun,$kecamatan,$status));
	}

	function get_sum_ipkd_tahun_kecamatan_bayar($tahun,$kecamatan,$status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($tahun,$kecamatan,$status));
	}

	function get_ipkd_tahun_kecamatan_bayar($tahun,$kecamatan,$status){
		$sql = "SELECT * FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND YEAR(`TGL_PEMBAYARAN`) = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND KECAMATAN = ? AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($tahun,$kecamatan,$status));
	}

	function get_sum_ipkd_kecamatan_bayar($kecamatan,$status){
		$sql = "SELECT SUM(RETRIBUSI) AS JUMLAH_RETRIBUSI FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND KECAMATAN = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($kecamatan,$status));
	}

	function get_ipkd_kecamatan_bayar($kecamatan, $status){
		$sql = "SELECT * FROM IPKD, PEMBAYARAN WHERE IPKD.IDIPKD = PEMBAYARAN.IDIPKD AND KECAMATAN = ? AND YEAR(`TGL_PEMBAYARAN`) <= YEAR(`SAMPAI_PERIJINAN`) AND IPKD.STATUS = 1 AND STATUS_BAYAR = ? "; 
		return $this->db->query($sql, array($kecamatan, $status));
	}
		
}//end of class